<?php
// 专门查询订单57的物料计算情况

// 数据库连接信息
$host = '127.0.0.1';
$dbname = 'scanwork';
$username = 'scanwork';
$password = 'rDezxK9h7tWp5sP7';
$port = 3306;

$orderId = 57; // 要查询的订单ID

try {
    // 创建数据库连接
    $pdo = new PDO("mysql:host=$host;port=$port;dbname=$dbname;charset=utf8mb4", $username, $password);
    $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    
    echo "\n=== 订单57物料计算情况查询 ===\n\n";
    
    // 1. 先查看订单表结构，确认正确字段名
    echo "查询订单表结构...\n";
    $stmt = $pdo->query("DESCRIBE fa_scanwork_order");
    $orderColumns = $stmt->fetchAll(PDO::FETCH_ASSOC);
    echo "订单表字段: " . implode(', ', array_column($orderColumns, 'Field')) . "\n\n";
    
    // 2. 查看订单型号表结构
    echo "查询订单型号表结构...\n";
    $stmt = $pdo->query("DESCRIBE fa_scanwork_order_model");
    $modelColumns = $stmt->fetchAll(PDO::FETCH_ASSOC);
    echo "订单型号表字段: " . implode(', ', array_column($modelColumns, 'Field')) . "\n\n";
    
    // 3. 查看订单物料表结构
    echo "查询订单物料表结构...\n";
    $stmt = $pdo->query("DESCRIBE fa_scanwork_order_material");
    $materialColumns = $stmt->fetchAll(PDO::FETCH_ASSOC);
    echo "订单物料表字段: " . implode(', ', array_column($materialColumns, 'Field')) . "\n\n";
    
    // 4. 查询订单基本信息
    $stmt = $pdo->prepare("SELECT * FROM fa_scanwork_order WHERE id = ?");
    $stmt->execute([$orderId]);
    $orderInfo = $stmt->fetch(PDO::FETCH_ASSOC);
    
    echo "订单基本信息：\n";
    echo "订单ID: " . (isset($orderInfo['id']) ? $orderInfo['id'] : 'N/A') . "\n";
    echo "订单编号: " . (isset($orderInfo['order_no']) ? $orderInfo['order_no'] : 'N/A') . "\n";
    echo "订单名称: " . (isset($orderInfo['name']) ? $orderInfo['name'] : 'N/A') . "\n";
    echo "创建时间: " . (isset($orderInfo['createtime']) ? $orderInfo['createtime'] : 'N/A') . "\n";
    echo "总数量: " . (isset($orderInfo['total_quantity']) ? $orderInfo['total_quantity'] : 'N/A') . "\n\n";
    
    // 5. 查询订单型号信息
    $stmt = $pdo->prepare("SELECT * FROM fa_scanwork_order_model WHERE order_id = ?");
    $stmt->execute([$orderId]);
    $orderModels = $stmt->fetchAll(PDO::FETCH_ASSOC);
    
    echo "订单型号信息：\n";
    if (!empty($orderModels)) {
        $modelsWithMaterials = 0;
        $totalProcessMaterials = 0;
        
        foreach ($orderModels as $model) {
            echo "型号ID: " . (isset($model['id']) ? $model['id'] : 'N/A') . ", ";
            echo "数量: " . (isset($model['quantity']) ? $model['quantity'] : 'N/A') . "\n";
            
            // 检查是否有product_model_id或类似字段
            $modelIdField = '';
            foreach (array_keys($model) as $key) {
                if (stripos($key, 'model_id') !== false) {
                    $modelIdField = $key;
                    break;
                }
            }
            
            if ($modelIdField && isset($model[$modelIdField])) {
                // 6. 查询该型号的工序物料配置
                $stmt2 = $pdo->prepare("SELECT pm.*, m.name as material_name, p.name as process_name 
                                        FROM fa_scanwork_process_material pm
                                        LEFT JOIN fa_scanwork_material m ON pm.material_id = m.id
                                        LEFT JOIN fa_scanwork_process p ON pm.process_id = p.id
                                        WHERE pm.model_id = ?");
                $stmt2->execute([$model[$modelIdField]]);
                $processMaterials = $stmt2->fetchAll(PDO::FETCH_ASSOC);
                
                $processCount = count($processMaterials);
                if ($processCount > 0) {
                    $modelsWithMaterials++;
                    $totalProcessMaterials += $processCount;
                    
                    echo "  工序物料配置: (共" . $processCount . "条)\n";
                    foreach ($processMaterials as $pm) {
                        echo "    工序: " . (isset($pm['process_name']) ? $pm['process_name'] : 'N/A') . ", ";
                        echo "物料: " . (isset($pm['material_name']) ? $pm['material_name'] : 'N/A') . ", ";
                        echo "单件用量: " . (isset($pm['unit_usage']) ? $pm['unit_usage'] : 'N/A') . ", ";
                        echo "损耗率: " . (isset($pm['loss_rate']) ? $pm['loss_rate'] : 'N/A') . "%\n";
                    }
                }
            }
        }
        
        echo "\n工序物料配置统计：\n";
        echo "- 有物料配置的型号数: $modelsWithMaterials\n";
        echo "- 总工序物料配置数: $totalProcessMaterials\n\n";
    }
    
    // 7. 查询订单物料需求结果（不连接工序表，只连接物料表）
    $stmt3 = $pdo->prepare("SELECT om.*, m.name as material_name
                            FROM fa_scanwork_order_material om
                            LEFT JOIN fa_scanwork_material m ON om.material_id = m.id
                            WHERE om.order_id = ?");
    $stmt3->execute([$orderId]);
    $orderMaterials = $stmt3->fetchAll(PDO::FETCH_ASSOC);
    
    echo "订单物料需求结果: (共" . count($orderMaterials) . "条)\n";
    if (!empty($orderMaterials)) {
        foreach ($orderMaterials as $om) {
            echo "物料: " . (isset($om['material_name']) ? $om['material_name'] : 'N/A') . ", ";
            echo "总需求量: " . (isset($om['total_quantity']) ? $om['total_quantity'] : 'N/A') . ", ";
            echo "单价: " . (isset($om['unit_price']) ? $om['unit_price'] : 'N/A') . ", ";
            echo "总成本: " . (isset($om['total_cost']) ? $om['total_cost'] : 'N/A') . "\n";
            
            // 打印所有存在的字段，便于调试
            echo "  其他字段: ";
            $otherFields = [];
            foreach ($om as $key => $value) {
                if (!in_array($key, ['id', 'material_id', 'total_quantity', 'unit_price', 'total_cost', 'material_name'])) {
                    $otherFields[] = "$key: $value";
                }
            }
            echo implode(', ', $otherFields) . "\n";
        }
    } else {
        echo "没有找到订单57的物料需求记录\n";
    }
    
    echo "\n=== 查询完成 ===\n";
    
} catch (PDOException $e) {
    echo "数据库连接或查询失败：" . $e->getMessage() . "\n";
} catch (Exception $e) {
    echo "查询出错：" . $e->getMessage() . "\n";
}